(ns forum.models
  (:require [clojure.java.jdbc :as sql])
  (:use korma.db
        korma.core))

(defdb forum 
  (mysql 
    {:db "forum"
     :host "localhost"
     :port "3306" 
     :user "forum"
     :password "forum"}))

(def posts)
(def users)
(def categories)
(def topics)
(def news)
(def messages)

(defentity categories
  (has-many topics))

(defentity topics
  (belongs-to categories)
  (has-many posts))

(defentity posts
  (belongs-to users)
  (belongs-to topics))

(defentity users
  (has-many posts))

(defentity news
  (belongs-to users))

(defentity messages)

(defn find-topics-by-category
  [id]
  (let [category-id (Integer/parseInt id)]
    (select topics (where {:categories_id category-id}))))

(defn find-posts-by-topic
  [id]
  (let [topic-id (Integer/parseInt id)]
    (select posts (where {:topics_id topic-id}))))

(defn find-user-by-username
  [username]
    (first (select users (where {:username username}))))

(defn find-user-by-id
  [id]
    (first (select users (where {:id id}))))

(defn delete-category-by-title
  [title]
  (delete categories (where {:title title})))

(defn delete-topic-by-title
  [title]
  (delete topics (where {:title title})))

(defn delete-post-by-content
  [content]
  (delete posts (where {:content content})))

(defn delete-post-by-id
  [id]
  (delete posts (where {:id id})))

(defn delete-posts-in-topic
  [id]
  (delete posts (where {:topics_id id})))

(defn delete-topic-by-id
  [id]
  (do
    (delete-posts-in-topic id)
    (delete topics (where {:id id}))
  ))

(defn delete-user-by-username
  [username]
  (delete users (where {:username username})))

(defn find-post-by-id
  [id]
  (first (select posts (where {:id (Integer/parseInt id)}))))

(defn find-topic-by-id
  [id]
  (first (select topics (where {:id (Integer/parseInt id)}))))

(defn generate-id [t]
  (if (empty? (select t))
  1
  (inc (:id (last (select t))))))

(defn generate-posts-id []
  (if (empty? (select posts))
  1
  (inc (:id (last (select posts))))))

(defn generate-topics-id []
  (if (empty? (select topics))
  1
  (inc (:id (last (select topics))))))

(defn delete-news-by-title
  [title]
  (delete news (where {:title title})))

(defn find-message-by-id
  [id]
    (first (select messages (where {:id id}))))

(defn find-posts-by-key
  [key]
  (select posts (where (like :content (str "%" key"%")))))

(defn search-posts [keys]
  (distinct (flatten (map find-posts-by-key (clojure.string/split keys #"\s+")))))

;(insert  users (values {:id 1, :username "admin", :password "admin", :role "admin", :email "mail AT vesko.com"}) )
;(insert categories (values {:id 1, :title "first category",:description "This is the first category created on this forum"}))
;(insert topics (values {:id 1, :title "first topic", :categories_id 1, :users_id 1} ))
;(insert posts (values 
;                {:id 1, :content "blabla2", :users_id 1, :topics_id 1}))